IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Utility_Asset_Distribution_InsertPhases')
	DROP PROCEDURE up_Utility_Asset_Distribution_InsertPhases
GO

CREATE PROCEDURE [dbo].[up_Utility_Asset_Distribution_InsertPhases]

AS

--Updates 3 phase
INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT
	Utility_Asset_DistributionFk, 3 --3 phase ID
FROM
	tb_Distributions_TopTypes t
INNER JOIN tb_Utility_Asset_Distribution_TopsXPole txp
	ON txp.TopTypeFk = t.Distributions_TopTypeId
WHERE
	SUBSTRING (Name, 1, 1) = 'C'
	AND	Utility_Asset_DistributionFk NOT IN (SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole )
GROUP BY 
	Utility_Asset_DistributionFk

--Updates 2 phase
INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT
	Utility_Asset_DistributionFk, 2 --2 phase ID
FROM
	tb_Distributions_TopTypes t
INNER JOIN tb_Utility_Asset_Distribution_TopsXPole txp
	ON txp.TopTypeFk = t.Distributions_TopTypeId
WHERE
	SUBSTRING (Name, 1, 1) = 'B'
	AND	Utility_Asset_DistributionFk NOT IN (SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole )
GROUP BY 
	Utility_Asset_DistributionFk


--Updates single phase
INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT
	Utility_Asset_DistributionFk, 1 --single phase ID
FROM
	tb_Distributions_TopTypes t
INNER JOIN tb_Utility_Asset_Distribution_TopsXPole txp
	ON txp.TopTypeFk = t.Distributions_TopTypeId
WHERE
	SUBSTRING (Name, 1, 1) = 'A'
	AND	Utility_Asset_DistributionFk NOT IN (SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole )
GROUP BY 
	Utility_Asset_DistributionFk


--Updates double phase
INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT
	Utility_Asset_DistributionFk, 4 --Double phase ID
FROM
	tb_Distributions_TopTypes t
INNER JOIN tb_Utility_Asset_Distribution_TopsXPole txp
	ON txp.TopTypeFk = t.Distributions_TopTypeId
WHERE
	SUBSTRING (Name, 1, 1) = 'D'
	AND	Utility_Asset_DistributionFk NOT IN (SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole )
GROUP BY 
	Utility_Asset_DistributionFk



--Poles without tops will have single phase
INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT Utility_Asset_DistributionId, 1
FROM tb_Utility_Asset_Distributions
WHERE Utility_Asset_DistributionId NOT IN 
	(
		SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole
	)
AND	Utility_Asset_DistributionId NOT IN (SELECT Utility_Asset_DistributionFk FROM tb_Utility_Asset_Distribution_PhasesXPole )

 